<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use DateTime;
use fast\Http;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Db;
use think\exception\DbException;
use think\exception\PDOException;
use think\exception\ValidateException;
use think\Log;
use think\process\pipes\Windows;
use think\Request;
use think\Response;
use think\response\Json;

/**
 * 订单处理器
 *
 * @icon fa fa-circle-o
 */
class Orderimporter extends Backend
{

    /**
     * Tickets模型对象
     * @var \app\admin\model\Proccessor
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Proccessor;
        $countries = \app\admin\model\Country::all();
        $countryList = [];
        foreach ($countries as $item) {
            $countryList[$item['id']] = $item['name'];
        }

        $shops = \app\admin\model\Shops::all();
        $shopList = [];
        foreach ($shops as $item) {
            $shopList[$item['id']] = $item['name'];
        }

        $goodsSkus = \app\admin\model\Goodssku::all();
        Log::error('22');
        $goodsSkuList = [];
        foreach ($goodsSkus as $item) {
            $goodsSkuList[$item['id']] = $item['sku_name'] . '-' . $item['goods_sku'];
        }
        Log::error('333');
        //var_dump($shopNo);
        $this->view->assign("countryList", $countryList);
        $this->view->assign("shopList", $shopList);
        $this->view->assign("goodsSkuList", $goodsSkuList);
        $this->view->assign("regionList", $this->model->getRegionList());
        $this->view->assign("shopNoList", $this->model->getShopNoList());
    }

    /**
     * 查看
     *
     * @return string|Json
     * @throws \think\Exception
     * @throws DbException
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        $result = ['total' => 0, 'rows' => []];
        if (false === $this->request->isAjax()) {

            //return         header('Location: http://localsheets.com/67dd4e3aaa734.xlsx',false);

            return $this->view->fetch();
        }

        return json($result);
    }

    /**
     * 查看
     *
     * @return string|Json
     * @throws \think\Exception
     * @throws DbException
     */
    public function shop()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        $result = ['total' => 2, 'rows' => [1 => "越南", 2 => "泰国"]];

        return json($result);
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    /**
     * 添加
     *
     * @return string
     * @throws \think\Exception
     */
    public function add()
    {
        if (false === $this->request->isPost()) {
            return $this->view->fetch();
        }
        $params = $this->request->post('row/a');

        if (empty($params)) {
            $this->error(__('Parameter %s can not be empty', ''));
        }

        $countryId = $params['country_id'];
        if (empty($countryId)) {
            $this->error(__('Country_id can not be empty', ''));
        }

        $shopId = $params['shop_id'];
        if (empty($shopId)) {
            $this->error(__('shop_id can not be empty', ''));
        }

        $goodsskuId = $params['goodssku_id'];
        if (empty($goodsskuId)) {
            $this->error(__('goodssku_id can not be empty', ''));
        }

        $cost = $params['cost'];
        if (empty($cost)) {
            $this->error(__('cost can not be empty', ''));
        }

        ini_set("memory_limit", "-1");

        //订单元数据
        $orderSheetsPath = ROOT_PATH . 'public/' . $params['avatar'];
        //结算单

        $datas = $this->handleOrderByCountry($countryId, $shopId, $goodsskuId, $params, $orderSheetsPath);

        if ($datas['code'] != 200) {
            $this->error($datas['msg']);
        }

        $this->success('成功', '');
    }

    private function handleOrderByCountry($countryId, $shopId, $goodsskuId, $params, $orderSheetsPath)
    {
        $allianceCommissionRate = $params['alliance_commission_rate'];
        $cost = $params['cost'];
        $chinaExtra = $params['china_extra'];
        $overseaExtra = $params['oversea_extra'];
        $tradeCommissionRate = $params['trade_commission_rate'];
        $tkHandlingRate = $params['tk_handling_rate'];
        $tkHandlingFee = $params['tk_handling_fee'];
        $sfpRate = $params['sfp_rate'];
        $superCouponRate = $params['super_coupon_rate'];
        $bonusCashRate = $params['bonus_cash_rate'];
        $tariffRate = $params['tariff_rate'];
        $vatRate = $params['value_added_rate'];
        $exportTransportFee = $params['export_transport_fee']; //跨境物流费用（国内发）
        $overseaOrderExportFee = $params['oversea_order_export_rate']; //海外仓头程费用
        $overseaOrderPackFee = $params['oversea_order_pack_rate']; //海外仓操作费
        $overseaOrderXujianFee = $params['oversea_order_xujian_fee']; //海外仓续件费
        
        $goodsSku = \app\admin\model\Goodssku::get(['id' => $goodsskuId]);
        $goodsId = $goodsSku['goods_id'];

        $country = \app\admin\model\Country::get(['id' => $countryId]);
        $currencyRate = $country['exchange_rate'];

        $spreadsheet = IOFactory::load($orderSheetsPath);
        //$spreadsheet->getSheet()
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数

        $datas = [];

        if ($countryId == \app\admin\model\Country::VN) {
            for ($row = 3; $row <= $highestRow; ++$row) {
                $skuId = $worksheet->getCell('G' . $row)->getValue(); //SKUID
                $canceled = $worksheet->getCell('D' . $row)->getValue(); //已取消
                $orderId = $worksheet->getCell('A' . $row)->getValue(); //订单ID
                $excelSku = $worksheet->getCell('G' . $row)->getValue(); //Excel里的SKU
                $quantity = $worksheet->getCell('J' . $row)->getValue(); //数量
                $orderAmount = $worksheet->getCell('M' . $row)->getValue(); //订单总额
                $tk_discount_amount = $worksheet->getCell('N' . $row)->getValue();//订单平台折扣
                $sellerDiscount = $worksheet->getCell('O' . $row)->getValue();//订单卖家折扣
                $warehouseNameO = $worksheet->getCell('AH' . $row)->getValue(); //仓库发货地址
                $deliverType = $worksheet->getCell('AJ' . $row)->getValue(); //运营发货类型
                $shippingType = strstr($deliverType, '全球') ? '0' : '1';
                $shipping_time = $worksheet->getCell('AB' . $row)->getValue();
                $delivered_time = $worksheet->getCell('AC' . $row)->getValue();
                $refund_amount = $worksheet->getCell('X' . $row)->getValue();
                $customer_transport_fee = $worksheet->getCell('R' . $row)->getValue();//买家订单运费
                $tk_transport_fee_discount = $worksheet->getCell('T' . $row)->getValue();//订单运费平台折扣
                $shop_discount_amount = $worksheet->getCell('S' . $row)->getValue();//订单运费商家折扣
                $actual_shipping_fee = $customer_transport_fee - $tk_transport_fee_discount - $shop_discount_amount; //买家实付运费
                $orderPaidAmount = $worksheet->getCell('W' . $row)->getValue();//订单买家实付；
                $orderCommissionBase = $orderAmount - $sellerDiscount; //平台佣金：是按照订单总金额（无任何平台补贴和卖家优惠即原价划线价得出的价格）- 卖家折扣
                /*
                备注：如果卖家做了包邮或者运费折扣，那么这块会计入成本
                */

                if ($excelSku == $goodsSku['goods_sku']) {
                    $item = [
                        'shops_id' => $shopId,
                        'order_no' => strval($orderId),
                        'goods_id' => $goodsId,
                        'goodssku_id' => $goodsskuId,
                        'order_amount' => $worksheet->getCell('P' . $row)->getValue(),//订单总金额
                        'tk_discount_amount' => $worksheet->getCell('N' . $row)->getValue(),//订单平台折扣
                        'customer_transport_fee' => $worksheet->getCell('R' . $row)->getValue(),//买家订单运费
                        'tk_transport_fee_discount' => $worksheet->getCell('T' . $row)->getValue(),//订单运费平台折扣
                        'shop_discount_amount' => $worksheet->getCell('S' . $row)->getValue(),//订单运费商家折扣
                        'actual_order_amount' => $orderCommissionBase,//订单实际收入
                        'order_create_time' => date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $worksheet->getCell('Y' . $row)->getValue())->getTimestamp()),//订单创建时间
                        'order_status' => $worksheet->getCell('B' . $row)->getValue(),//订单状态
                        'refund_amount' => empty($refund_amount) ? 0 : $refund_amount,//订单退款金额
                        'shipping_time' => empty($shipping_time) ? 0 : date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $shipping_time)->getTimestamp()),//订单发货时间
                        'delivered_time' => empty($delivered_time) ? 0 : date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $delivered_time)->getTimestamp()),//订单送达时间
                        'shipping_type' => $shippingType,//订单发货类型:0=国内发货,1=海外仓发货
                        'goods_cost' => $cost * $quantity,//产品成本
                        'extra_fee' => $shippingType == 0 ? $chinaExtra * $quantity : 0,//产品杂费
                        'tk_handle_fee' => bcdiv($orderPaidAmount * $tkHandlingRate / 100, $currencyRate, 2),//平台手续费，根据买家实付计算
                        'tk_commission_fee' => bcdiv($orderCommissionBase * $tradeCommissionRate / 100, $currencyRate, 2),//平台佣金
                        'added_value_fee' => $shippingType == 0 ? bcdiv($orderPaidAmount * $vatRate / 100, $currencyRate, 2) : 0,//增值税
                        'tariff_fee' => $shippingType == 0 ? bcdiv($orderPaidAmount * $tariffRate / 100, $currencyRate, 2) : 0,//关税tariff_rate
                        'sfp_fee' => bcdiv($orderCommissionBase * $sfpRate / 100, $currencyRate, 2),//SFP费用sfp_rate
                        'bonus_cash_fee' => bcdiv($orderCommissionBase * $bonusCashRate/ 100, $currencyRate, 2),//奖金返现服务费bonus_cash_rate
                        'super_coupon_fee' => bcdiv($orderCommissionBase * $superCouponRate / 100, $currencyRate, 2),//超级优惠券费用
                        'affiliate_fee' => bcdiv($orderCommissionBase * $allianceCommissionRate / 100, $currencyRate, 2),//达人联盟佣金 //todo 10个点, 也是按商品原价-卖家折扣后作为基数计算
                        'china_shipping_fee' => $shippingType == 0 ? bcdiv($exportTransportFee * $quantity, $currencyRate, 2) : 0,//预计国内发货物流成本
                        'oversea_shipping_fee' => $shippingType == 1 ? $overseaOrderExportFee * $quantity : 0,//跨境头程成本
                        'oversea_pack_fee' => $shippingType == 1 ? ($overseaOrderPackFee + ($quantity-1) * $overseaOrderXujianFee) : 0,//跨境海外仓成本
                        'oversea_extra_fee' => $shippingType == 1 ? $overseaExtra * $quantity : 0,//跨境杂费成本
                        'tk_vn_handle_fee' => bcdiv($tkHandlingFee, $currencyRate, 2),
                        'quantity' => $quantity,
                    ];
    

                    if ($orderCommissionBase == 0) {
                        //样品，收入为0，不用扣平台扣点，达人佣金，其它产品成本和物流成本照扣
                        //样品同时会产生尾程运费，但订单Excel不会体现该费用，需要等结算才能明细
                        $estimated_profit = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                        $item['estimated_profit'] = $estimated_profit;
                    } else {
                        $estimated_profit = bcdiv($orderCommissionBase, $currencyRate, 2) - $item['goods_cost'] - $item['extra_fee'] - $item['tk_handle_fee'] - $item['tk_commission_fee'] - $item['added_value_fee'] - $item['tariff_fee'] - $item['sfp_fee'] - $item['bonus_cash_fee'] - $item['super_coupon_fee'] - $item['affiliate_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'] - $item['tk_vn_handle_fee'];
                        $item['estimated_profit'] = $estimated_profit;
                    }
                    

                    $exist = \app\admin\model\Order::get(['order_no' => $orderId]);
                    if (empty($exist)) {
                        if ($item['order_status'] == '已取消' && !empty($shipping_time)) {
                            //如果订单已取消，但已发货，计入亏损
                            //todo
                            //更新订单状态为‘未送达’，预计收益为负，但不会存在平台各项费用（佣金，手续费，优惠券）
                            $item['order_status'] = '未送达';
                            $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                            \app\admin\model\Order::create($item);

                        } elseif ($item['order_status'] == '已取消' && empty($shipping_time)) {
                            //客户直接取消了，并且没有之前导入的同订单，不做处理，不用插入数据库
                            Log::info('1');
                        } elseif ($item['order_status'] == '已发货') {
                            //直接插入订单数据，核算预计利润
                            \app\admin\model\Order::create($item);
                        } elseif ($item['order_status'] == '待发货') {
                            //直接插入订单数据，核算预计利润
                            \app\admin\model\Order::create($item);
                        } elseif ($item['order_status'] == '已完成') {
                            //直接插入订单数据，核算预计利润
                            if ($canceled == 'Return/Refund') {
                                $item['order_status'] = '已退款';
                                $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                                \app\admin\model\Order::create($item);
                            } else {
                                \app\admin\model\Order::create($item);
                            }   
                        }

                    } else {
                        //已取消，未发货
                        if ($item['order_status'] == '已取消' && empty($shipping_time)) {
                            //更新订单状态为已取消，利润清零，所有成本清0
                            \app\admin\model\Order::update(['order_status' => '已取消', 'estimated_profit' => 0], ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '已取消' && !empty($shipping_time)) {
                            $item['order_status'] = '未送达';
                            $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '已发货') {
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '待发货') {
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] != '已完成') {
                            if ($canceled == 'Return/Refund') {
                                $item['order_status'] = '已退款';
                                $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                                \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                            } else {
                                \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                            }
                        }
                    }
                }
            }
        }

        if ($countryId == \app\admin\model\Country::TH) {
            for ($row = 3; $row <= $highestRow; ++$row) {
                $skuId = $worksheet->getCell('G' . $row)->getValue(); //SKUID
                $canceled = $worksheet->getCell('D' . $row)->getValue(); //已取消
                $orderId = $worksheet->getCell('A' . $row)->getValue(); //订单ID
                $excelSku = $worksheet->getCell('G' . $row)->getValue(); //Excel里的SKU
                $quantity = $worksheet->getCell('J' . $row)->getValue(); //数量
                $orderAmount = $worksheet->getCell('M' . $row)->getValue(); //订单总额
                $tk_discount_amount = $worksheet->getCell('N' . $row)->getValue();//订单平台折扣
                $sellerDiscount = $worksheet->getCell('O' . $row)->getValue();//订单卖家折扣
                $warehouseNameO = $worksheet->getCell('AH' . $row)->getValue(); //仓库发货地址
                $deliverType = $worksheet->getCell('AJ' . $row)->getValue(); //运营发货类型
                $shippingType = strstr($deliverType, '全球') ? '0' : '1';
                $shipping_time = $worksheet->getCell('AB' . $row)->getValue();
                $delivered_time = $worksheet->getCell('AC' . $row)->getValue();
                $refund_amount = $worksheet->getCell('X' . $row)->getValue();
                $customer_transport_fee = $worksheet->getCell('R' . $row)->getValue();//买家订单运费
                $tk_transport_fee_discount = $worksheet->getCell('T' . $row)->getValue();//订单运费平台折扣
                $shop_discount_amount = $worksheet->getCell('S' . $row)->getValue();//订单运费商家折扣
                $actual_shipping_fee = $customer_transport_fee - $tk_transport_fee_discount - $shop_discount_amount; //买家实付运费
                $orderPaidAmount = $worksheet->getCell('W' . $row)->getValue();//订单买家实付；
                $orderCommissionBase = $orderAmount - $sellerDiscount; //平台佣金：是按照订单总金额（无任何平台补贴和卖家优惠即原价划线价得出的价格）- 卖家折扣
                /*
                备注：如果卖家做了包邮或者运费折扣，那么这块会计入成本
                */

                if ($excelSku == $goodsSku['goods_sku']) {
                    $item = [
                        'shops_id' => $shopId,
                        'order_no' => strval($orderId),
                        'goods_id' => $goodsId,
                        'goodssku_id' => $goodsskuId,
                        'order_amount' => $worksheet->getCell('P' . $row)->getValue(),//订单总金额
                        'tk_discount_amount' => $worksheet->getCell('N' . $row)->getValue(),//订单平台折扣
                        'customer_transport_fee' => $worksheet->getCell('R' . $row)->getValue(),//买家订单运费
                        'tk_transport_fee_discount' => $worksheet->getCell('T' . $row)->getValue(),//订单运费平台折扣
                        'shop_discount_amount' => $worksheet->getCell('S' . $row)->getValue(),//订单运费商家折扣
                        'actual_order_amount' => $orderCommissionBase,//订单实际收入
                        'order_create_time' => date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $worksheet->getCell('Y' . $row)->getValue())->getTimestamp()),//订单创建时间
                        'order_status' => $worksheet->getCell('B' . $row)->getValue(),//订单状态
                        'refund_amount' => empty($refund_amount) ? 0 : $refund_amount,//订单退款金额
                        'shipping_time' => empty($shipping_time) ? 0 : date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $shipping_time)->getTimestamp()),//订单发货时间
                        'delivered_time' => empty($delivered_time) ? 0 : date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $delivered_time)->getTimestamp()),//订单送达时间
                        'shipping_type' => $shippingType,//订单发货类型:0=国内发货,1=海外仓发货
                        'goods_cost' => $cost * $quantity,//产品成本
                        'extra_fee' => $shippingType == 0 ? $chinaExtra * $quantity : 0,//产品杂费
                        'tk_handle_fee' => bcdiv($orderPaidAmount * $tkHandlingRate / 100, $currencyRate, 2),//平台手续费，根据买家实付计算
                        'tk_commission_fee' => bcdiv($orderCommissionBase * $tradeCommissionRate / 100, $currencyRate, 2),//平台佣金
                        'added_value_fee' => $shippingType == 0 ? bcdiv($orderPaidAmount * $vatRate / 100, $currencyRate, 2) : 0,//增值税
                        'tariff_fee' => $shippingType == 0 ? bcdiv($orderPaidAmount * $tariffRate / 100, $currencyRate, 2) : 0,//关税tariff_rate
                        'sfp_fee' => bcdiv($orderCommissionBase * $sfpRate / 100, $currencyRate, 2),//SFP费用sfp_rate
                        'bonus_cash_fee' => bcdiv($orderCommissionBase * $bonusCashRate/ 100, $currencyRate, 2),//奖金返现服务费bonus_cash_rate
                        'super_coupon_fee' => bcdiv($orderCommissionBase * $superCouponRate / 100, $currencyRate, 2),//超级优惠券费用
                        'affiliate_fee' => bcdiv($orderCommissionBase * $allianceCommissionRate / 100, $currencyRate, 2),//达人联盟佣金 //todo 10个点, 也是按商品原价-卖家折扣后作为基数计算
                        'china_shipping_fee' => $shippingType == 0 ? bcdiv($exportTransportFee * $quantity, $currencyRate, 2) : 0,//预计国内发货物流成本
                        'oversea_shipping_fee' => $shippingType == 1 ? $overseaOrderExportFee * $quantity : 0,//跨境头程成本
                        'oversea_pack_fee' => $shippingType == 1 ? ($overseaOrderPackFee + ($quantity-1) * $overseaOrderXujianFee) : 0,//跨境海外仓成本
                        'oversea_extra_fee' => $shippingType == 1 ? $overseaExtra * $quantity : 0,//跨境杂费成本
                        'tk_vn_handle_fee' => bcdiv($tkHandlingFee, $currencyRate, 2),
                        'quantity' => $quantity,
                    ];

                    if ($orderCommissionBase == 0) {
                        //样品，收入为0，不用扣平台扣点，达人佣金，其它产品成本和物流成本照扣
                        //样品同时会产生尾程运费，但订单Excel不会体现该费用，需要等结算才能明细
                        $estimated_profit = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                        $item['estimated_profit'] = $estimated_profit;
                    } else {
                        $estimated_profit = bcdiv($orderCommissionBase, $currencyRate, 2) - $item['goods_cost'] - $item['extra_fee'] - $item['tk_handle_fee'] - $item['tk_commission_fee'] - $item['added_value_fee'] - $item['tariff_fee'] - $item['sfp_fee'] - $item['bonus_cash_fee'] - $item['super_coupon_fee'] - $item['affiliate_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'] - $item['tk_vn_handle_fee'];
                        $item['estimated_profit'] = $estimated_profit;
                    }
                    

                    $exist = \app\admin\model\Order::get(['order_no' => $orderId]);
                    if (empty($exist)) {
                        if ($item['order_status'] == '已取消' && !empty($shipping_time)) {
                            //如果订单已取消，但已发货，计入亏损
                            //todo
                            //更新订单状态为‘未送达’，预计收益为负，但不会存在平台各项费用（佣金，手续费，优惠券）
                            $item['order_status'] = '未送达';
                            $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                            \app\admin\model\Order::create($item);

                        } elseif ($item['order_status'] == '已取消' && empty($shipping_time)) {
                            //客户直接取消了，并且没有之前导入的同订单，不做处理，不用插入数据库
                            Log::info('1');
                        } elseif ($item['order_status'] == '已发货') {
                            //直接插入订单数据，核算预计利润
                            \app\admin\model\Order::create($item);
                        } elseif ($item['order_status'] == '待发货') {
                            //直接插入订单数据，核算预计利润
                            \app\admin\model\Order::create($item);
                        } elseif ($item['order_status'] == '已完成') {
                            //直接插入订单数据，核算预计利润
                            if ($canceled == 'Return/Refund') {
                                $item['order_status'] = '已退款';
                                $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                                \app\admin\model\Order::create($item);
                            } else {
                                \app\admin\model\Order::create($item);
                            }   
                        }

                    } else {
                        //已取消，未发货
                        if ($item['order_status'] == '已取消' && empty($shipping_time)) {
                            //更新订单状态为已取消，利润清零，所有成本清0
                            \app\admin\model\Order::update(['order_status' => '已取消', 'estimated_profit' => 0], ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '已取消' && !empty($shipping_time)) {
                            $item['order_status'] = '未送达';
                            $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '已发货') {
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '待发货') {
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] != '已完成') {
                            if ($canceled == 'Return/Refund') {
                                $item['order_status'] = '已退款';
                                $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                                \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                            } else {
                                \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                            }
                        }
                    }
                }
            }
        }

        if ($countryId == \app\admin\model\Country::MY) {
            for ($row = 3; $row <= $highestRow; ++$row) {
                $skuId = $worksheet->getCell('G' . $row)->getValue(); //SKUID
                $canceled = $worksheet->getCell('D' . $row)->getValue(); //已取消
                $orderId = $worksheet->getCell('A' . $row)->getValue(); //订单ID
                $excelSku = $worksheet->getCell('G' . $row)->getValue(); //Excel里的SKU
                $quantity = $worksheet->getCell('J' . $row)->getValue(); //数量
                $orderAmount = $worksheet->getCell('M' . $row)->getValue(); //订单总额
                $tk_discount_amount = $worksheet->getCell('N' . $row)->getValue();//订单平台折扣
                $sellerDiscount = $worksheet->getCell('O' . $row)->getValue();//订单卖家折扣
                $warehouseNameO = $worksheet->getCell('AH' . $row)->getValue(); //仓库发货地址
                $deliverType = $worksheet->getCell('AJ' . $row)->getValue(); //运营发货类型
                $shippingType = strstr($deliverType, '全球') ? '0' : '1';
                $shipping_time = $worksheet->getCell('AB' . $row)->getValue();
                $delivered_time = $worksheet->getCell('AC' . $row)->getValue();
                $refund_amount = $worksheet->getCell('X' . $row)->getValue();
                $customer_transport_fee = $worksheet->getCell('R' . $row)->getValue();//买家订单运费
                $tk_transport_fee_discount = $worksheet->getCell('T' . $row)->getValue();//订单运费平台折扣
                $shop_discount_amount = $worksheet->getCell('S' . $row)->getValue();//订单运费商家折扣
                $actual_shipping_fee = $customer_transport_fee - $tk_transport_fee_discount - $shop_discount_amount; //买家实付运费
                $orderPaidAmount = $worksheet->getCell('W' . $row)->getValue();//订单买家实付；
                $orderCommissionBase = $orderAmount - $sellerDiscount; //平台佣金：是按照订单总金额（无任何平台补贴和卖家优惠即原价划线价得出的价格）- 卖家折扣
                /*
                备注：如果卖家做了包邮或者运费折扣，那么这块会计入成本
                */

                Log::error($excelSku);
                Log::error($goodsSku['goods_sku']);

                if ($excelSku == $goodsSku['goods_sku']) {
                    $item = [
                        'shops_id' => $shopId,
                        'order_no' => strval($orderId),
                        'goods_id' => $goodsId,
                        'goodssku_id' => $goodsskuId,
                        'order_amount' => $worksheet->getCell('P' . $row)->getValue(),//订单总金额
                        'tk_discount_amount' => $worksheet->getCell('N' . $row)->getValue(),//订单平台折扣
                        'customer_transport_fee' => $worksheet->getCell('R' . $row)->getValue(),//买家订单运费
                        'tk_transport_fee_discount' => $worksheet->getCell('T' . $row)->getValue(),//订单运费平台折扣
                        'shop_discount_amount' => $worksheet->getCell('S' . $row)->getValue(),//订单运费商家折扣
                        'actual_order_amount' => $orderCommissionBase,//订单实际收入
                        'order_create_time' => date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $worksheet->getCell('Y' . $row)->getValue())->getTimestamp()),//订单创建时间
                        'order_status' => $worksheet->getCell('B' . $row)->getValue(),//订单状态
                        'refund_amount' => empty($refund_amount) ? 0 : $refund_amount,//订单退款金额
                        'shipping_time' => empty($shipping_time) ? 0 : date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $shipping_time)->getTimestamp()),//订单发货时间
                        'delivered_time' => empty($delivered_time) ? 0 : date('Y-m-d H:i:s', DateTime::createFromFormat('d/m/Y H:i:s', $delivered_time)->getTimestamp()),//订单送达时间
                        'shipping_type' => $shippingType,//订单发货类型:0=国内发货,1=海外仓发货
                        'goods_cost' => $cost * $quantity,//产品成本
                        'extra_fee' => $shippingType == 0 ? $chinaExtra * $quantity : 0,//产品杂费
                        'tk_handle_fee' => bcdiv($orderPaidAmount * $tkHandlingRate / 100, $currencyRate, 2),//平台手续费，根据买家实付计算
                        'tk_commission_fee' => bcdiv($orderCommissionBase * $tradeCommissionRate / 100, $currencyRate, 2),//平台佣金
                        'added_value_fee' => $shippingType == 0 ? bcdiv($orderPaidAmount * $vatRate / 100, $currencyRate, 2) : 0,//增值税
                        'tariff_fee' => $shippingType == 0 ? bcdiv($orderPaidAmount * $tariffRate / 100, $currencyRate, 2) : 0,//关税tariff_rate
                        'sfp_fee' => bcdiv($orderCommissionBase * $sfpRate / 100, $currencyRate, 2),//SFP费用sfp_rate
                        'bonus_cash_fee' => bcdiv($orderCommissionBase * $bonusCashRate/ 100, $currencyRate, 2),//奖金返现服务费bonus_cash_rate
                        'super_coupon_fee' => bcdiv($orderCommissionBase * $superCouponRate / 100, $currencyRate, 2),//超级优惠券费用
                        'affiliate_fee' => bcdiv($orderCommissionBase * $allianceCommissionRate / 100, $currencyRate, 2),//达人联盟佣金 //todo 10个点, 也是按商品原价-卖家折扣后作为基数计算
                        'china_shipping_fee' => $shippingType == 0 ? bcdiv($exportTransportFee * $quantity, $currencyRate, 2) : 0,//预计国内发货物流成本
                        'oversea_shipping_fee' => $shippingType == 1 ? $overseaOrderExportFee * $quantity : 0,//跨境头程成本
                        'oversea_pack_fee' => $shippingType == 1 ? ($overseaOrderPackFee + ($quantity-1) * $overseaOrderXujianFee) : 0,//跨境海外仓成本
                        'oversea_extra_fee' => $shippingType == 1 ? $overseaExtra * $quantity : 0,//跨境杂费成本
                        'tk_vn_handle_fee' => bcdiv($tkHandlingFee, $currencyRate, 2),
                        'quantity' => $quantity,
                    ];

                    if ($orderCommissionBase == 0) {
                        //样品，收入为0，不用扣平台扣点，达人佣金，其它产品成本和物流成本照扣
                        //样品同时会产生尾程运费，但订单Excel不会体现该费用，需要等结算才能明细
                        $estimated_profit = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                        $item['estimated_profit'] = $estimated_profit;
                    } else {
                        $estimated_profit = bcdiv($orderCommissionBase, $currencyRate, 2) - $item['goods_cost'] - $item['extra_fee'] - $item['tk_handle_fee'] - $item['tk_commission_fee'] - $item['added_value_fee'] - $item['tariff_fee'] - $item['sfp_fee'] - $item['bonus_cash_fee'] - $item['super_coupon_fee'] - $item['affiliate_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'] - $item['tk_vn_handle_fee'];
                        $item['estimated_profit'] = $estimated_profit;
                    }
                    

                    $exist = \app\admin\model\Order::get(['order_no' => $orderId]);
                    if (empty($exist)) {
                        if ($item['order_status'] == '已取消' && !empty($shipping_time)) {
                            //如果订单已取消，但已发货，计入亏损
                            //todo
                            //更新订单状态为‘未送达’，预计收益为负，但不会存在平台各项费用（佣金，手续费，优惠券）
                            $item['order_status'] = '未送达';
                            $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                            \app\admin\model\Order::create($item);

                        } elseif ($item['order_status'] == '已取消' && empty($shipping_time)) {
                            //客户直接取消了，并且没有之前导入的同订单，不做处理，不用插入数据库
                            Log::info('1');
                        } elseif ($item['order_status'] == '已发货') {
                            //直接插入订单数据，核算预计利润
                            \app\admin\model\Order::create($item);
                        } elseif ($item['order_status'] == '待发货') {
                            //直接插入订单数据，核算预计利润
                            \app\admin\model\Order::create($item);
                        } elseif ($item['order_status'] == '已完成') {
                            //直接插入订单数据，核算预计利润
                            if ($canceled == 'Return/Refund') {
                                $item['order_status'] = '已退款';
                                $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                                \app\admin\model\Order::create($item);
                            } else {
                                \app\admin\model\Order::create($item);
                            }   
                        }

                    } else {
                        //已取消，未发货
                        if ($item['order_status'] == '已取消' && empty($shipping_time)) {
                            //更新订单状态为已取消，利润清零，所有成本清0
                            \app\admin\model\Order::update(['order_status' => '已取消', 'estimated_profit' => 0], ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '已取消' && !empty($shipping_time)) {
                            $item['order_status'] = '未送达';
                            $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '已发货') {
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] == '待发货') {
                            \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                        } elseif ($item['order_status'] != '已完成') {
                            if ($canceled == 'Return/Refund') {
                                $item['order_status'] = '已退款';
                                $item['estimated_profit'] = 0 - $item['goods_cost'] - $item['extra_fee'] - $item['china_shipping_fee'] - $item['oversea_shipping_fee'] - $item['oversea_pack_fee'] - $item['oversea_extra_fee'];
                                \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                            } else {
                                \app\admin\model\Order::update($item, ['order_no' => $orderId]);
                            }
                        }
                    }
                }
            }
        }


        return ['code' => 200, 'msg' => 'ok', 'data' => $datas];
    }
}
